File manager - Edit - /home/autoph/public_html/projects/app/Http/Controllers/ReportController.php
Back
<?php namespace App\Http\Controllers; use App\Models\EmployeeLeave; use Carbon\Carbon; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; class ReportController extends Controller { public function generateReport(Request $request) { // dd($request->all()); switch($request->modules){ case '1': return $this->generateLeaveReport($request); break; case '2': return $this->generateOvertimeReport($request); break; default: return response()->json(['message' => 'You need to select module']); } } public function generateLeaveReport($request) { // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required|array', 'from_date' => 'required|date', 'to_date' => 'required|date', 'leave_id' => 'nullable|integer', 'status' => 'nullable|string', ]); // Start the query $query = DB::table('employee_leaves') ->join('employees', 'employee_leaves.employee_id', '=', 'employees.employee_id') ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id'); // Apply filters if ($request->filled('leave_id')) { $query->where('employee_leaves.leave_id', $request->leave_id); } if ($request->filled('status')) { $query->where('employee_leaves.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_leaves.date_from', [$fromDate, $toDate]); $query->whereBetween('employee_leaves.date_to', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Conditionally include status if 'status' is in $columns if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_leaves.status = 0 THEN 'Pending / For Recommendation' WHEN employee_leaves.status = 1 THEN 'For Approval' WHEN employee_leaves.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } // Handle transformed columns dynamically if (in_array('leave_id', $selectedColumns)) { $transformedColumns[] = 'leave_types.name as leave_id'; } if (in_array('without_pay', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.without_pay = 0 THEN 'No' ELSE 'Yes' END as without_pay"); } if (in_array('half_day', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.half_day = 0 THEN 'No' ELSE 'Yes' END as half_day"); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_leaves.description as description'); } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_leaves.created_at as created_at'); } // Conditionally join employees for recommending_id if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_leaves.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } // Conditionally join employees for approver_id if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_leaves.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $leaves = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $leaves, 'total_hours' => $query->sum('hours'), // This line can also be used if not grouping 'total_days' => $query->sum('days'), // This line can also be used if not grouping ]); } public function generateOvertimeReport($request) { // dd($request->all()); // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required|array', 'from_date' => 'required|date', 'to_date' => 'required|date', 'charge_to' => 'nullable|integer', 'status' => 'nullable|string', ]); // Start the query $query = DB::table('employee_overtimes') ->join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id') ->leftjoin('companies', 'employee_overtimes.charge_to', '=', 'companies.id'); // Apply filters if ($request->filled('charge_to')) { $query->where('employee_overtimes.charge_to', $request->charge_to); } if ($request->filled('status')) { $query->where('employee_overtimes.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_overtimes.date_from', [$fromDate, $toDate]); $query->whereBetween('employee_overtimes.date_to', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Conditionally include status if 'status' is in $columns if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_overtimes.status = 0 THEN 'Pending / For Recommendation' WHEN employee_overtimes.status = 1 THEN 'For Approval' WHEN employee_overtimes.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } // Handle transformed columns dynamically if (in_array('charge_to', $selectedColumns)) { $transformedColumns[] = 'companies.name as charge_to'; } if (in_array('meal', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.meal = 0 THEN 'No' ELSE 'Yes' END as meal"); } if (in_array('transportation', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.transportation = 0 THEN 'No' ELSE 'Yes' END as transportation"); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_overtimes.description as description'); } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_overtimes.created_at as created_at'); } // Conditionally join employees for recommending_id if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_overtimes.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } // Conditionally join employees for approver_id if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_overtimes.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $leaves = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $leaves, 'total_hours' => $query->sum('hours'), // This line can also be used if not grouping ]); } }
| ver. 1.4 |
.
| PHP 8.1.32 | Generation time: 0 |
proxy
|
phpinfo
|
Settings